using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using AAM.SQL;
using CMS.BIZ;
using AAM.BIZ;
using System.Collections;

namespace CMS.BOP
{
    public class SqlBizPageProvider : SiteMapProvider
    {

        private Connection connection;

        public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
        {
            base.Initialize(name, config);
            string connectionStringName = config["connectionStringName"];
            connection = ConnectionManager.Instance.OpenConnection(connectionStringName);
        }

        protected SiteMapNode CreateFromReader(DataReader reader)
        {
            int tid = (int)reader["PageID"];
            try
            {
                ObjectID oid = new ObjectID(BizPage.ObjectType, new object[] { tid });
                IBizObject obj = IdentityMap.Instance[oid];
                return (SiteMapNode)obj;
            }
            catch (KeyNotFoundException)
            {
                int? parentID = (int?)reader["ParentID"];
                string name = (string)reader["PageName"];
                string rolesAuthorized = (string)reader["AuthorizedRoles"];
                string description = (string)reader["Description"];
                List<string> listRoles = new List<string>();
                if (!string.IsNullOrEmpty(rolesAuthorized))
                {
                    string[] roles = rolesAuthorized.Split(new char[] { ',' });
                    foreach (string role in roles)
                    {
                        listRoles.Add(role);
                    }
                }
                BizPage node = new BizPage(this, tid, name, parentID, description, listRoles);
                node.PageIndex = (int)reader["PageIndex"];
                node.PageTitle = (string)reader["PageTitle"];
                node.MasterPageFile = (string)reader["MasterPageFile"];
                node.ContentID = (int?)reader["ContentID"];
                node.RequireAuthentication = (bool)reader["RequireAuthentication"];

                IdentityMap.Instance.Register(node);
                return node;
            }
        }
        protected SiteMapNodeCollection CreateCollectionFromReader(DataReader reader)
        {
            SiteMapNodeCollection collection = new SiteMapNodeCollection();
            while (reader.Read())
            {
                collection.Add(CreateFromReader(reader));
            }
            return collection;
        }
        protected List<BizPage> CreateListFromReader(DataReader reader)
        {
            List<BizPage> list = new List<BizPage>();
            while (reader.Read())
            {
                list.Add((BizPage)CreateFromReader(reader));
            }
            return list;
        }
        public override SiteMapNode FindSiteMapNode(string rawUrl)
        {
            string toFind = "Page=";
            int index = rawUrl.IndexOf(toFind);
            if (index != -1)
            {
                index += toFind.Length;
                int endIndex = rawUrl.IndexOf("&", index);
                if (endIndex == -1) endIndex = rawUrl.Length;
                int length = (endIndex - index);
                string strPageID = rawUrl.Substring(index, length);
                try
                {
                    int pageID = int.Parse(strPageID);
                    Command command = connection.CreateCommand("SELECT * FROM Pages WHERE PageID=?");
                    command.AddParameter(pageID);
                    DataReader reader = command.ExecuteReader();
                    SiteMapNode node = null;
                    if (reader.Read())
                    {
                        node = CreateFromReader(reader);
                    }
                    reader.Close();
                    command.Dispose();
                    return node;
                }
                catch (Exception)
                {
                }
            }
            return GetRootNodeCore();
        }
        public override SiteMapNodeCollection GetChildNodes(SiteMapNode node)
        {
            BizPage page = (BizPage)node;
            Command command = connection.CreateCommand("SELECT * FROM Pages WHERE ParentID=? ORDER BY PageIndex");
            command.AddParameter(page.PageID);
            DataReader reader = command.ExecuteReader();
            SiteMapNodeCollection collection = CreateCollectionFromReader(reader);
            reader.Close();
            command.Dispose();
            return collection;
        }
        public override SiteMapNode GetParentNode(SiteMapNode node)
        {
            BizPage page = (BizPage)node;
            if (page.ParentID == null) return null;
            Command command = connection.CreateCommand("SELECT * FROM Pages WHERE PageID=?");
            command.AddParameter(page.ParentID);
            DataReader reader = command.ExecuteReader();
            node = null;
            if (reader.Read())
            {
                node = CreateFromReader(reader);
            }
            reader.Close();
            command.Dispose();
            return node;
        }
        protected override SiteMapNode GetRootNodeCore()
        {
            Command command = connection.CreateCommand("SELECT * FROM Pages WHERE ParentID IS NULL");
            DataReader reader = command.ExecuteReader();
            SiteMapNode node = null;
            if (reader.Read())
            {
                node = CreateFromReader(reader);
            }
            reader.Close();
            command.Dispose();
            return node;
        }
        public List<BizPage> GetAll()
        {
            Command command = connection.CreateCommand("SELECT * FROM Pages ORDER BY ParentID, PageIndex");
            DataReader reader = command.ExecuteReader();
            List<BizPage> list = CreateListFromReader(reader);
            reader.Close();
            command.Dispose();
            return list;
        }
        public BizPage Read(int id)
        {
            Command command = connection.CreateCommand("SELECT * FROM Pages WHERE PageID=?");
            command.AddParameter(id);
            DataReader reader = command.ExecuteReader();
            BizPage page = null;
            if (reader.Read())
            {
                page = (BizPage)CreateFromReader(reader);
            }
            reader.Close();
            command.Dispose();
            return page;
        }
        public bool UpdateChild(int? parentId, int? newParentId)
        {
            Command command;
            if(parentId == null) 
            {
                command = connection.CreateCommand("UPDATE Pages SET ParentID=? WHERE ParentID IS NULL");
                command.AddParameter(newParentId);
            } 
            else 
            {
                command  = connection.CreateCommand("UPDATE Pages SET ParentID=? WHERE ParentID=?");
                command.AddParameter(newParentId);
                command.AddParameter(parentId);
            }
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public bool Create(BizPage obj)
        {
            Command command = connection.CreateCommand("INSERT INTO Pages(ParentID,PageName," +
                "AuthorizedRoles,Description,PageIndex,PageTitle,MasterPageFile,ContentID,"+
                "RequireAuthentication) VALUES(?,?,?,?,?,?,?,?,?)");
            command.AddParameter(obj.ParentID);
            command.AddParameter(obj.PageName);
            command.AddParameter(obj.AuthorizedRolesString);
            command.AddParameter(obj.Description);
            command.AddParameter(obj.PageIndex);
            command.AddParameter(obj.PageTitle);
            command.AddParameter(obj.MasterPageFile);
            command.AddParameter(obj.ContentID);
            command.AddParameter(obj.RequireAuthentication);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            if (result > 0)
            {
                command = connection.CreateCommand("SELECT @@IDENTITY");
                int lastId = (int)command.ExecuteScalar();
                command.Dispose();
                obj.PageID = lastId;
            }
            return result > 0;
        }
        public bool Update(BizPage obj)
        {
            Command command = connection.CreateCommand("UPDATE Pages SET ParentID=?,PageName=?," +
                "AuthorizedRoles=?,Description=?,PageIndex=?,PageTitle=?,MasterPageFile=?,"+
                "ContentID=?,RequireAuthentication=? WHERE PageID=?");
            command.AddParameter(obj.ParentID);
            command.AddParameter(obj.PageName);
            command.AddParameter(obj.AuthorizedRolesString);
            command.AddParameter(obj.Description);
            command.AddParameter(obj.PageIndex);
            command.AddParameter(obj.PageTitle);
            command.AddParameter(obj.MasterPageFile);
            command.AddParameter(obj.ContentID);
            command.AddParameter(obj.RequireAuthentication);
            command.AddParameter(obj.PageID);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            return result > 0;
        }
        public bool Delete(int id)
        {
            Command command = connection.CreateCommand("DELETE FROM Pages WHERE PageID=?");
            command.AddParameter(id);
            int result = command.ExecuteNonQuery();
            command.Dispose();
            if (result > 0)
            {
                IdentityMap.Instance.Unregister(new ObjectID(BizPage.ObjectType, new object[] { id }));
                UpdateChild(id, null);
            }
            return result > 0;
        }
    }
}
